package com.yz.dba.controller;

import com.yz.common.utils.JdbcUtils;
import com.yz.common.utils.Result;
import com.yz.common.utils.StatusCode;
import com.yz.dba.service.DbaService;
import com.yz.common.pojo.Database;
import com.yz.common.pojo.DbLink;
import com.yz.common.pojo.Task;
import com.yz.dba.utils.CSVUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 段成
 * @date 2019/12/5 - 10:14
 */
@RestController
@RequestMapping("/dbaController")
@CrossOrigin
public class DbaController {
    @Autowired
    private DbaService dbaService;

    /**
     * 获取所有的连接
     * @return
     */
    @GetMapping("/getLinkName")
    public Result getLinkName(){

        List<DbLink> linkNameList = dbaService.getLinkName();
        if(linkNameList!=null){
            return new Result(true, StatusCode.OK,"查询成功",linkNameList,"获取所有的连接");
        }
        return new Result(false, StatusCode.ERROR,"查询失败","获取所有的连接");

    }

    /**
     * 根据选择的连接获取该连接下所有数据库
     * @param linkId
     * @return
     */
    @GetMapping("/getDatabaseByLinkId/{linkId}")
    public Result getDatabaseByLinkId(@PathVariable("linkId") Integer linkId){
        List<Database> databaseList = dbaService.getDatabaseByLinkId(linkId);
        if(databaseList!=null){
            return new Result(true, StatusCode.OK,"查询成功",databaseList,"根据选择的连接获取该连接下所有数据库");
        }
        return new Result(false, StatusCode.ERROR,"查询失败","根据选择的连接获取该连接下所有数据库");
    }

    /**
     * 根据数据库名获取该数据库下所有表名
     * @param databaseId
     * @return
     */
    @GetMapping("/getTablesByDatabaseId/{databaseId}")
    public Result getTablesByDatabaseId(@PathVariable("databaseId") Integer databaseId){

        List<String> tableList = dbaService.getTablesByDatabaseId(databaseId);
        if(tableList!=null){
            return new Result(true, StatusCode.OK,"查询成功",tableList,"获取数据库下所有表名");
        }
        return new Result(false, StatusCode.ERROR,"查询失败","获取数据库下所有表名");
    }

    /**
     * 根据表名和数据库id获取该表所有字段
     * @param databaseId
     * @param tableName
     * @return
     */
    @GetMapping("/getfieldNamesByDatabaseNameAndTableName/{databaseId}/{tableName}")
    public Result getfieldNamesByDatabaseNameAndTableName(@PathVariable("databaseId") Integer databaseId,
                                                          @PathVariable("tableName") String tableName){
        List<String> fieldNames = dbaService.getfieldNamesByDatabaseNameAndTableName(databaseId,tableName);

        if(fieldNames!=null){
            return new Result(true, StatusCode.OK,"查询成功",fieldNames,"获取表所有字段");
        }
        return new Result(false, StatusCode.ERROR,"查询失败","获取表所有字段");
    }

    /**
     * 提交迁移任务  等待管理员审核
     * @param task ----------------------------->使用postman测试 传对象用json所以会+@RequestBody 正式上线根据前端传来的参数类型判断是否删除此注解
     * @return
     */
    @PostMapping("/commitTask")
    public Result commitTask(@RequestBody Task task) throws SQLException {
        //task 从前端传来的数据没有id,submitId,solveId,taskNumber,taskTypeName,status,cdate,pdate,dstatus,level
        Boolean flag = this.checkTableRepeat(task.getTargetTableName(),task.getTargetDatabaseId());

        //flag==true  说明表不存在 添加任务
        if(flag){
            StringBuffer fieldString = new StringBuffer();
            for (String field : task.getFieldNamesArray()) {
                fieldString.append(field);
                fieldString.append(",");
            }
            task.setFieldNames(fieldString.toString().substring(0,fieldString.toString().length()-1));
            dbaService.commitTask(task);
            return new Result(true, StatusCode.OK,"提交成功","提交迁移任务");
        }else {
            return new Result(false, StatusCode.ERROR,"表名已存在","提交迁移任务");
        }

    }

    public Boolean checkTableRepeat(String targetTableName,Integer targetDatabaseId) throws SQLException {


        //通过targetDatabaseId查询基本四项 再查询是否表已存在
        Database database = dbaService.getDatabaseLinkIdById(targetDatabaseId);
        DbLink dbLink = dbaService.getDbLinkByLinkId(database.getLinkId());
        StringBuffer sql = new StringBuffer();
        sql.append("select COUNT(1) as count from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='");
        sql.append(database.getDatabaseName());
        sql.append("' and TABLE_NAME='");
        sql.append(targetTableName);
        sql.append("'");
        Map map = getMapByDbLink(dbLink, database.getUsername(), database.getPass());
        Connection connection = JdbcUtils.getConnection(map);
        PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        String string = null;
        while (resultSet.next()) {
            string = resultSet.getString(1);
        }
        if("0".equals(string)){
            //表不存在
            return true;
        }
        return false;
    }

    //获取基本四项map
    public Map getMapByDbLink(DbLink dbLink, String username, String password){
        StringBuffer url = new StringBuffer();
        // ==1 说明是mysql
        if(dbLink.getDbType() == 1){
            url.append("jdbc:mysql://");
            url.append(dbLink.getIp());
            url.append(":");
            url.append(dbLink.getPort());
            url.append("/mysql?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC");
        }

        Map map=new HashMap();
        map.put("driver", dbLink.getDriver());
        map.put("url", url.toString());
        map.put("username", username);
        map.put("pass", password);

        return map;
    }


    /**
     * 迁移数据
     * @param taskId
     * @return
     */
    @GetMapping("/dataMigration/{taskId}")
    public Result dataMigration(@PathVariable("taskId") Integer taskId){
        //查询任务
        Task task = (Task) this.findTaskById(taskId).getData();
        dbaService.dataMigration(task);
        return new Result(true, StatusCode.OK,"迁移成功","迁移数据");
    }

    /**
     * 查询任务byId
     * @return
     */
    @GetMapping("/findTask/{taskId}")
    public Result findTaskById(@PathVariable("taskId") Integer taskId){
        Task task = dbaService.findTaskById(taskId);
        return new Result(true, StatusCode.OK,"查询成功",task,"查询任务");
    }

    /**
     * 查询任务列表
     * @return
     */
    @GetMapping("/findTask")
    public Result findTask(@RequestParam String username){
        List<Task> taskList = dbaService.findTask(username);
        return new Result(true, StatusCode.OK,"查询成功",taskList,"查询任务列表");
    }


    /**
     * 删除任务
     * @param taskId
     * @return
     */
    @GetMapping("/deleteTaskById/{taskId}")
    public Result deleteTaskById(@PathVariable Integer taskId){
        int flag = dbaService.deleteTaskById(taskId);
        if(flag!=-1){
            return new Result(true, StatusCode.OK,"删除成功","删除成功");
        }
        return new Result(false,StatusCode.ERROR,"删除失败","删除成功");
    }



    /**
     * 提交核心
     * @param task
     * @return
     */
    @PostMapping("/commitCore")
    public Result commitCore(@RequestBody Task task){
        dbaService.commitTask(task);
        return new Result(true, StatusCode.OK,"提交成功","提交核心迁移任务");
    }

    /**
     * 迁移核心数据
     * @param taskId
     * @return
     */
    @GetMapping("/dataMigrationCore/{taskId}")
    public Result dataMigrationCore(@PathVariable("taskId") Integer taskId){
        //查询任务
        Task task = (Task) this.findTaskById(taskId).getData();
        dbaService.dataMigrationCore(task);

        return new Result(true, StatusCode.OK,"迁移成功","迁移核心数据");
    }

    @GetMapping("/getCoreFields")
    public Result getCoreFields(){
        List<String> list = dbaService.getCoreFields();
        return new Result(true, StatusCode.OK,"查询成功",list,"查询核心表字段");
    }

}
